﻿using LFramework.Data;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace DataServices
{
    /*
     * 陈伟
     * 20140831
     * 顾问操作类
     */
    public class DaoArtist
    {
        IDBHelper dbhelper = DBFactory.CreateFromConfig("conn");

        /// <summary>
        /// 根据顾问id显示顾问明细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ArtistInfo LoadArtistByID(string id)
        {
            string sql = "select * from tbArtist where ArtistID = '" + id + "'";

            DataRow dr = dbhelper.QueryForDataTable(sql).Rows[0];

            ArtistInfo artist = new ArtistInfo()
            {
                ArtistID = dr["ArtistID"].ToString(),
                ArtistName = dr["ArtistName"].ToString(),
                Abstract = dr["Abstract"].ToString(),
                ArtGroup = dr["ArtGroup"].ToString()
            };


            DataTable dtAtta = dbhelper.QueryForDataTable("select * from tbNewsAttachment where NewsId = '" + id + "'");

            foreach (DataRow drAtta in dtAtta.Rows)
            {
                AttachmentInfo attachment = new AttachmentInfo();
                attachment.FileName = drAtta["FileName"].ToString();
                attachment.SaveName = drAtta["SaveName"].ToString();
                artist.AttachmentList.Add(attachment);
            }

            return artist;
        }

        /// <summary>
        /// 查询所有顾问
        /// </summary>
        /// <returns></returns>
        public IList<ArtistInfo> LoadAllArtists()
        {
            IList<ArtistInfo> list = new List<ArtistInfo>();
            string sql = "select * from tbArtist";
            DataTable dt = dbhelper.QueryForDataTable(sql);
            foreach (DataRow dr in dt.Rows)
            {
                ArtistInfo Artist = new ArtistInfo()
                {
                    ArtistID = dr["ArtistID"].ToString(),
                    ArtistName = dr["ArtistName"].ToString(),
                    Abstract = dr["Abstract"].ToString()
                };
                //查询顾问对应的图片信息
                DataTable dtAtta = dbhelper.QueryForDataTable("select * from tbNewsAttachment where NewsId = '" + Artist.ArtistID + "'");
                if (dtAtta.Rows.Count > 0)
                {
                    foreach (DataRow drAtta in dtAtta.Rows)
                    {
                        AttachmentInfo attachment = new AttachmentInfo();
                        attachment.FileName = drAtta["FileName"].ToString();
                        attachment.SaveName = drAtta["SaveName"].ToString();
                        Artist.AttachmentList.Add(attachment);
                    }
                }
                list.Add(Artist);
            }
            return list;
        }

        public void AddArtist(ArtistInfo artist)
        {
            string sql = @"insert into tbArtist(
                            [ArtistID]
                           ,[ArtistName]
                           ,[Abstract]
                           ,[ArtGroup]) 
                           values (
                            @ArtistID,
                            @ArtistName,
                            @Abstract,
                            @ArtGroup
                            )  
                           ";

            OleDbParameter[] paras = { new OleDbParameter("ArtistID",artist.ArtistID),
                                     new OleDbParameter("ArtistName", artist.ArtistName) ,
                                     new OleDbParameter("Abstract", artist.Abstract),
                                     new OleDbParameter("ArtGroup",artist.ArtGroup)
                                   };

            dbhelper.ExecuteSQL_InTrans(sql, paras);

            foreach (var item in artist.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + artist.ArtistID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public void UpdateArtist(ArtistInfo artist)
        {
            string sql = @"
             update tbArtist 
             set ArtistName = @ArtistName , Abstract = @Abstract , ArtGroup = @ArtGroup
             where ArtistID = '" + artist.ArtistID + "'";

            OleDbParameter[] paras = { new OleDbParameter("ArtistName", artist.ArtistName) , 
                                       new OleDbParameter("Abstract",artist.Abstract),
                                        new OleDbParameter("ArtGroup",artist.ArtGroup)
                                   };

            dbhelper.ExecuteSQL(sql, paras);

            //更新附件列表
            sql = "delete from tbNewsAttachment where NewsId = '" + artist.ArtistID + "'";
            dbhelper.ExecuteSQL(sql);

            foreach (var item in artist.AttachmentList)
            {
                sql = "insert into tbNewsAttachment(NewsID ,FileName ,SaveName) values('" + artist.ArtistID + "','" + item.FileName + "','" + item.SaveName + "')";
                dbhelper.ExecuteSQL(sql);
            }
        }

        public void DeleteArtist(string id)
        {
            string sql = "delete from tbNewsAttachment where NewsId = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
            sql = "delete from tbArtist where ArtistID = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
        }
    }
}
